package com.jzhao.gper.orm.Dao;

import com.jzhao.gper.orm.po.Member;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseDao<T> {

    Connection con = null;
    PreparedStatement pstm = null;
    ResultSet rs = null;

    /**
     * 获取连接
     *
     * @return
     */
    private void getConnection() throws Exception {
            //1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            //2、建立连接,设置连接字符集
            //jdbc:mysql://localhost:3306/db_cjky?useUnicode=true&characterEncoding=utf8
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gp-vip-spring-db-demo?useUnicode=true&characterEncoding=utf8", "root", "6262");
    }

    /**
     * 根据po生成SQL
     *
     * @param obj
     * @return
     */
    private String generateSelectSqlByPo(T obj) throws Exception {
        StringBuffer sql = new StringBuffer("select * from ");
        Class<?> clazz = obj.getClass();
        //映射了表名
        if (clazz.isAnnotationPresent(Table.class)) {
            sql.append(clazz.getAnnotation(Table.class).name());
        } else {
            sql.append(clazz.getSimpleName());
        }
        sql.append(" where 1=1 ");

        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Transient.class)) {
                continue;
            }

            field.setAccessible(true);
            String fieldName = field.getName();
            if (field.isAnnotationPresent(Column.class)) {
                fieldName = field.getAnnotation(Column.class).name();
            }

            Object fieldValue = field.get(obj);

            if(null == fieldValue){continue;}

            if (fieldValue instanceof String) {
                sql.append(" and " + fieldName + "='" + fieldValue + "'");
            } else {
                sql.append(" and " + fieldName + "=" + fieldValue);
            }
        }
        return sql.toString();
    }

    /**
     * 根据po生成SQL
     *
     * @param obj
     * @return
     */
    private String generateInsertSqlByPo(T obj) throws Exception {
        StringBuffer sql = new StringBuffer("insert into ");
        //列名
        StringBuffer columns = new StringBuffer();
        //值
        StringBuffer values = new StringBuffer();
        Class<?> clazz = obj.getClass();
        //映射了表名
        if (clazz.isAnnotationPresent(Table.class)) {
            sql.append(clazz.getAnnotation(Table.class).name());
        } else {
            sql.append(clazz.getSimpleName());
        }
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Transient.class)) {
                continue;
            }
            field.setAccessible(true);
            String fieldName = field.getName();
            if (field.isAnnotationPresent(Column.class)) {
                fieldName = field.getAnnotation(Column.class).name();
            }
            Object fieldValue = field.get(obj);

            if(null == fieldValue){continue;}

            columns.append(fieldName+",");
            if (fieldValue instanceof String) {
                values.append("'" + fieldValue + "',");
            } else {
                values.append("'" + fieldValue + "',");
            }
        }
        //拼接列名、列值
        sql.append("(" +  columns.substring(0,columns.length()-1) + ") values (" + values.substring(0,values.length()-1) + ")");
        return sql.toString();
    }

    /**
     * 根据po更新SQL，必须要有ID
     *
     * @param obj
     * @return
     */
    private String generateUpdateSqlByPo(T obj) throws Exception {
        StringBuffer sql = new StringBuffer("update ");
        Class<?> clazz = obj.getClass();
        //映射了表名
        if (clazz.isAnnotationPresent(Table.class)) {
            sql.append(clazz.getAnnotation(Table.class).name() +" set ");
        } else {
            sql.append(clazz.getSimpleName() +" set ");
        }
        //id字段
        String idName = null;
        String idValue = null;

        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            if (field.isAnnotationPresent(Transient.class)) {
                continue;
            }
            field.setAccessible(true);
            String fieldName = field.getName();
            if (field.isAnnotationPresent(Column.class)) {
                fieldName = field.getAnnotation(Column.class).name();
            }
            Object fieldValue = field.get(obj);

            if(null == fieldValue){continue;}

            //根据ID更新
            if (field.isAnnotationPresent(Id.class)) {
                idName = fieldName;
                idValue = fieldValue.toString();
                continue;
            }

            if (fieldValue instanceof String) {
                sql.append(" " + fieldName + "='" + fieldValue + "',");
            } else {
                sql.append(" " + fieldName + "=" + fieldValue + ",");
            }
        }

        return sql.substring(0,sql.length()-1) + " where " + idName + "=" + idValue;
    }

    /**
     * 将结果集转成list
     * @param objClass
     * @return
     * @throws Exception
     */
    private List<T> rsToList(Class<?> objClass) throws Exception {
        if (rs == null) {
            return null;
        }
        List<T> result = new ArrayList<>();
        Field[] fields = objClass.getDeclaredFields();
        //5、获取结果集
        //返回的字段数
        int columnCount = rs.getMetaData().getColumnCount();
        //循环行
        while (rs.next()) {
            T obj = (T) objClass.newInstance();
            //循环列,设置对象属性
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rs.getMetaData().getColumnName(i);
                //根据列名，获取字段名
                Field field = getFieldNameByColumn(fields, columnName);
                if (null == field) {
                    continue;
                }
                field.setAccessible(true);
                field.set(obj, rs.getObject(columnName));
            }
            result.add(obj);
        }
        return result;
    }

    /**
     * 根据列名获取字段
     * @param fields
     * @param columnName
     * @return
     */
    private Field getFieldNameByColumn(Field[] fields, String columnName) {
        for (Field field : fields) {
            if (field.getName().equals(columnName)) {
                return field;
            }
            if (field.isAnnotationPresent(Column.class)
                    && null != field.getAnnotation(Column.class).name()
                    && field.getAnnotation(Column.class).name().equals(columnName)) {
                return field;
            }
        }
        return null;
    }

    private void closeCon() {
        //6、关闭连接
        try {
            if(null != rs)
                rs.close();
            if(null != pstm)
                pstm.close();
            if(null != con)
                con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 查询
     * @param obj
     * @return
     * @throws Exception
     */
    public List<T> select(T obj) throws Exception {
        List<T> result = null;
        try {
            getConnection();
            String sql = generateSelectSqlByPo(obj);
            System.out.println("sql="+sql);
            //3、创建语句集
            pstm = con.prepareStatement(sql);
            //4、执行语句集
            rs = pstm.executeQuery();
            //5、获取结果集
            result = rsToList(obj.getClass());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCon();
            return result;
        }
    }

    /**
     * 新增
     * @param obj
     * @return
     * @throws Exception
     */
    public boolean save(T obj) throws Exception {
        boolean result = false;
        try {
            getConnection();
            String sql = generateInsertSqlByPo(obj);
            System.out.println("sql="+sql);
            //3、创建语句集
            pstm = con.prepareStatement(sql);
            //4、执行语句集
            result = pstm.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCon();
            return result;
        }
    }

    /**
     * 新增
     * @param obj
     * @return
     * @throws Exception
     */
    public boolean update(T obj) throws Exception {
        boolean result = false;
        try {
            getConnection();
            String sql = generateUpdateSqlByPo(obj);
            System.out.println("sql="+sql);
            //3、创建语句集
            pstm = con.prepareStatement(sql);
            //4、执行语句集
            result = pstm.execute();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeCon();
            return result;
        }
    }
}
